﻿
using System;
using System.Collections;
using System.Collections.Generic;
using System.Data;
using System.Diagnostics;



public class DatabaseObjectsPlugin : IPlugin
{
    private List<ITemplate> _templates = new List<ITemplate>();
    const string PluginName = "SQL FK View Builder  Plugin";
    const string PluginDescription = "SQL FK View Builder Generator Plugin";
    public DatabaseObjectsPlugin()
    {
        _templates.Add(new FKViewsBuilder());
    }
    public string Description
    {
        get { return PluginName; }
    }

    public string Name
    {
        get { return PluginDescription; }
    }

    public System.Collections.Generic.List<ITemplate> Templates
    {
        get { return _templates; }
        set { _templates = value; }
    }

    public class FKViewsBuilder : ITemplate
    {

        const string TemplateName = "SQL FK View Builder Template";
        const string TemplateDescription = "SQL FK View Builder Template";
        const string TemplateOutputLanguage = "SQL";
        private ArrayList _arraylist = new ArrayList();
        private string _workingdir;
        private string _languageMappingFileName;
        private string _dbTargetMappingFileName;
        private const string GENERATED_FILE_NAME = ".sql";
        private const string GENERATED_FOLDER_NAME = ".SQL_Views\\";

        private ArrayList _refkeyList = new ArrayList();
        public bool Execute(ref MyMeta.IDatabase db, string workingDir, GenerationProject generationProject)
        {
            try
            {
                MyMeta.IDatabase _dataBase = db;

                System.Text.StringBuilder Output = new System.Text.StringBuilder();
                string refColumns = "";
                string refFkColumns = "";
                string refFkJoins = "";
                string strViewName = string.Empty;

                //string _mappingDirectory = System.Reflection.Assembly.GetCallingAssembly.Location.Substring(0, System.Reflection.Assembly.GetCallingAssembly.Location.LastIndexOf("\\")) + "\\mapping\\";

                //_dataBase.Root.LanguageMappingFileName = _mappingDirectory + "Languages.xml";
                //_dataBase.Root.DbTargetMappingFileName = _mappingDirectory + "DbTargets.xml";

                Output.AppendLine("--Script Generated By OpenORM.NET at :" + DateTime.Now.ToLongDateString());
                Output.AppendLine("--Plugin Version: " + System.Reflection.Assembly.GetExecutingAssembly().GetName().Version.ToString());
                Output.AppendLine("");
                Output.Append("USE [");
                Output.Append(_dataBase.Name);
                Output.AppendLine("]");
                Output.AppendLine("GO");
                // Loop through the tables the user select and generate the stored procs and save them to disk

                Output.AppendLine("");
                Output.AppendLine("BEGIN TRANSACTION");
                Output.AppendLine("");
                Output.AppendLine("--Generated By OpenORM.NET at :" + DateTime.Now.ToLongDateString());
                Output.AppendLine("");

                foreach (MyMeta.ITable refTable in _dataBase.Tables)
                {
                    _refkeyList = new ArrayList();
                    if (refTable.Selected)
                    {
                        string refTableName = refTable.Name;

                        if (refTable.PrimaryKeys.Count == 0)
                        {
                            Output.AppendLine("-- ERROR: Table '" + refTable.Name + "' must have a primary key");


                        }
                        else
                        {
                            strViewName = "_" + refTable.Name + "_VIEW";


                            Output.Append(BuildCreateAlterViewStatement(strViewName, refTable.Schema));

                            string refFrom = " FROM " + refTable.Name + Environment.NewLine;

                            Output.AppendLine("");
                            Output.AppendLine("AS");
                            Output.AppendLine("SELECT ");

                            refFkColumns = string.Empty;


                            foreach (MyMeta.IColumn refColumn in refTable.Columns)
                            {
                                refColumns += refTableName + "." + refColumn.Name + " As " + refTableName + "_" + refColumn.Name + "," + Environment.NewLine;

                                foreach (MyMeta.IForeignKey refFk in refColumn.ForeignKeys)
                                {
                                    MyMeta.ITable refFkTable = ((MyMeta.IForeignKey)refFk).PrimaryTable;
                                    //Dim refFkTableName As String = GetEntityName(refFkTable.Name)
                                    MyMeta.Column fkColumn = (MyMeta.Column)refFk.ForeignColumns[0];

                                    MyMeta.Column refFkJoinColumn = (MyMeta.Column)refFkTable.PrimaryKeys[0];
                                    string refFkTableName = refFkTable.Name;

                                    if (!refFkTable.Name.ToUpper().Equals(refTable.Name.ToUpper()))
                                    {
                                        _refkeyList.Add(refFkTable.Name);
                                        refFkJoins += " INNER JOIN " + refFkTable.Name + " As " + refFkTableName + "_" + fkColumn.Name + " ON " + refFkTableName + "_" + fkColumn.Name + "." + refFkJoinColumn.Name + " = " + refTableName + "." + fkColumn.Name + Environment.NewLine;

                                        foreach (MyMeta.IColumn refFkColumn in refFkTable.Columns)
                                        {
                                            refFkColumns += refFkTableName + "_" + fkColumn.Name + "." + refFkColumn.Name + " As " + refFkTableName + "_" + fkColumn.Name + "_" + refFkColumn.Name + "," + Environment.NewLine;
                                        }
                                    }
                                }
                            }
                            string refAllColumns = refColumns.ToString() + refFkColumns.ToString();
                            refAllColumns = refAllColumns.ToString().Substring(0, refAllColumns.ToString().Length - 3) + Environment.NewLine;
                            Output.AppendLine(refAllColumns + refFrom + refFkJoins.ToString());
                            Output.AppendLine("GO ");
                            refColumns = string.Empty;
                            refFkColumns = string.Empty;
                            refFkJoins = string.Empty;
                            refAllColumns = string.Empty;
                        }

                    }

                }


                Output.AppendLine("");
                Output.AppendLine("GO");
                Output.AppendLine("\tIF @@ERROR <> 0 ROLLBACK TRANSACTION;");
                Output.AppendLine("\tELSE ");
                Output.AppendLine("\tBEGIN");
                Output.AppendLine("\t\tCOMMIT TRANSACTION;");
                Output.AppendLine("\tEND");
                Output.AppendLine("");

                Output.AppendLine("-- " + "END " + strViewName);

                if (!_workingdir.EndsWith("\\"))
                    _workingdir += "\\";
                System.IO.Directory.CreateDirectory(_workingdir + _dataBase.Name.ToUpper() + GENERATED_FOLDER_NAME);
                string path = _workingdir + db.Name.ToUpper() + GENERATED_FOLDER_NAME + db.Name.ToUpper() + GENERATED_FILE_NAME;
                System.IO.StreamWriter sw = new System.IO.StreamWriter(path);


                sw.Write(Output.ToString());
                sw.Close();

                if (OnFileGenerated != null)
                {
                    OnFileGenerated(_workingdir + db.Name.ToUpper() + GENERATED_FOLDER_NAME + db.Name.ToUpper() + GENERATED_FILE_NAME);
                }
                return true;

            }
            catch (Exception ex)
            {
                if (OnException != null)
                {
                    OnException(ex);
                }
                return false;
            }
        }
        private string GetEntityName(string entityName)
        {
            Int32 count = default(Int32);
            string result = entityName;
            foreach (string entity in _refkeyList)
            {
                if (entity.Equals(entityName))
                {
                    count += 1;
                }
            }
            if (count > 0)
            {
                result += "_" + count.ToString();

            }
            return result;

        }
        public string BuildCreateAlterStatement(string strProcName, string strSchema)
        {

            string strStatement = null;
            strStatement = "";


            // Drop and recreate
            //strStatement = strStatement & "IF EXISTS (SELECT * FROM SYSOBJECTS WHERE ID = OBJECT_ID('" & strProcName & "') AND sysstat & 0xf = 4)" & vbCrLf
            strStatement = strStatement + "IF  EXISTS (SELECT * FROM sys.objects WHERE name = '" + strProcName + "' AND type in (N'V'))" + Environment.NewLine;

            strStatement = strStatement + "    DROP PROCEDURE [" + strSchema.Trim() + "].[" + strProcName + "];" + Environment.NewLine;
            strStatement = strStatement + "GO" + Environment.NewLine;

            strStatement = strStatement + "CREATE PROCEDURE [" + strSchema.Trim() + "].[" + strProcName + "]";


            return strStatement;

        }
        public string BuildCreateAlterViewStatement(string strViewName, string strSchema)
        {

            string strStatement = null;
            strStatement = "";


            strStatement = strStatement + "IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[" + strSchema.Trim() + "].[" + strViewName + "]') AND type in (N'V')" + Environment.NewLine;
            strStatement = strStatement + "    BEGIN " + Environment.NewLine;
            strStatement = strStatement + "         DROP VIEW [" + strSchema.Trim() + "].[" + strViewName + "];" + Environment.NewLine;
            strStatement = strStatement + "    END " + Environment.NewLine;
            strStatement = strStatement + "GO" + Environment.NewLine;

            strStatement = strStatement + "CREATE VIEW [" + strSchema.Trim() + "].[" + strViewName + "]";


            return strStatement;

        }

        public string Name
        {
            get { return TemplateName; }
        }


        public event OnExceptionEventHandler OnException;

        public string WorkingDir
        {
            get { return _workingdir; }
            set { _workingdir = value; }
        }

        public string Description
        {
            get { return TemplateDescription; }
        }

        public event OnInfoEventHandler OnInfo;


        public string OutputLanguage
        {
            get { return TemplateOutputLanguage; }
        }

        public event OnFileGeneratedEventHandler OnFileGenerated;

        public event OnPercentDoneEventHandler OnPercentDone;

        public string DbTargetMappingFileName
        {
            get { return _dbTargetMappingFileName; }
            set { _dbTargetMappingFileName = value; }
        }

        public string LanguageMappingFileName
        {
            get { return _languageMappingFileName; }
            set { _languageMappingFileName = value; }
        }

        public string GUID
        {
            get { return "27138c73-c79a-462a-8f51-3476991d42fd"; }
        }

        #region ITemplate Members


        public bool Execute(MyMeta.IDatabase db, string workingDir, GenerationProject generationProject)
        {
            return this.Execute(db, workingDir, generationProject);
        }

        #endregion
    }
    // End class FKViewsBuilder

}